﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: May 3, 2016
-- Description:	Takes external entity/relationship/other information and converts 
--				it into OPENQUERY syntax for placement in RafikiNOSQL queries.
-- =============================================
CREATE FUNCTION [dbo].[ConvertRafikiNOSQL_ExternalSQL] 
(
	-- This is a call to an external system.
	@QueryString nvarchar(max),
	@ViewName nvarchar(max),
	@SystemName nvarchar(MAX),
	@AliasName nvarchar(MAX),
	@WhereClause nvarchar(MAX),
	@E1 nvarchar(100) -- Entity1LinkID
)
RETURNS nvarchar(max)
AS
BEGIN
	DECLARE @DataForReplacement nvarchar(max)
	DECLARE @EXTViewType nvarchar(100) = NULL
	DECLARE @Entity1Link nvarchar(100) = NULL
	DECLARE @Entity1LinkValue nvarchar(100) = NULL

	SELECT @EXTViewType = ExtViewType,@Entity1Link = E1LinkName FROM DefinedExternalViews
	WHERE ViewName = @ViewName

	IF NOT @Entity1Link IS NULL AND @E1 <> '' BEGIN 
		SELECT @Entity1LinkValue = AttributeValueString FROM Attributes A INNER JOIN DefinedAttributes DA 
			ON DA.AttributeID = A.AttributeID AND AttributeName = @Entity1Link AND cast(EntityID AS NVARCHAR) = @E1
		--IF @Entity1LinkValue IS NULL BEGIN
		--	SET @DataForReplacement = 'Error: ' + @E1 + ' does not exist as an internal entity in the system.'
		--	RETURN @DataForReplacement
		--END
	END



	IF @EXTViewType = 'Entity' BEGIN
		SET @DataForReplacement = 'SELECT * FROM (' + @QueryString + ') '
		IF NOT @Entity1LinkValue IS NULL
			SET @DataForReplacement = @DataForReplacement + 'WHERE (' + @Entity1Link + ' = ""' + @Entity1LinkValue
								+ '"" OR ""' + @Entity1LinkValue + '"" IS NULL) '
		SET @DataForReplacement = 'OPENQUERY('+@SystemName+',''SELECT * FROM (' + @DataForReplacement + ') '
								+ @AliasName + ' ' + COALESCE(@WhereClause,'') + ''') AS ' + @AliasName + ' '
	END ELSE IF @EXTViewType = 'Relationship' BEGIN
		-- If the External View is a relationship and has an Entity1Link specified AND has a valid EntityID specified in E1, then continue.
		IF NOT @Entity1Link IS NULL AND @Entity1LinkValue <> '' BEGIN 
			SET @DataForReplacement = 'SELECT A.*,RTRIM(""' + @E1 + '"") AS Entity1ID FROM (' + @QueryString + ') A WHERE ' 
									+ '(' + @Entity1Link + ' = ""' + @Entity1LinkValue + '"" OR ""' + @Entity1LinkValue + '"" IS NULL) ' + 
									+ 'AND (""@ED"" IS NULL OR StartDate<=TO_DATE(""@ED"",''''MM/DD/YYYY'''') OR StartDate IS NULL) '
									+ 'AND (""@SD"" IS NULL OR EndDate>=TO_DATE(""@SD"",''''MM/DD/YYYY'''') OR EndDate IS NULL) '
			SET @DataForReplacement = 'OPENQUERY('+@SystemName+',''SELECT * FROM (' + @DataForReplacement + ') '
									+ @AliasName + ' ' + COALESCE(@WhereClause,'') + ''') AS ' + @AliasName + ' '
			SET @DataForReplacement = '(SELECT *,EntityName AS Entity1Name FROM ' + @DataForReplacement + ' INNER JOIN Entities E3 ' 
									+ ' ON ' + @AliasName + '.Entity1ID = E3.EntityID) AS ' + @AliasName + ' '
		END ELSE IF NOT @Entity1Link IS NULL BEGIN
			SET @DataForReplacement = 'SELECT * FROM (' + @QueryString + ') WHERE  ' + 
									+ '(""@ED"" IS NULL OR StartDate<=TO_DATE(""@ED"",''''MM/DD/YYYY'''') OR StartDate IS NULL) '
									+ 'AND (""@SD"" IS NULL OR EndDate>=TO_DATE(""@SD"",''''MM/DD/YYYY'''') OR EndDate IS NULL) '
			SET @DataForReplacement = 'OPENQUERY('+@SystemName+',''SELECT * FROM (' + @DataForReplacement + ') '
									+ @AliasName + ' ' + COALESCE(@WhereClause,'') + ''') AS ' + @AliasName + ' '
			SET @DataForReplacement = '(SELECT ' + @AliasName + '.*,E3.EntityID AS Entity1ID,EntityName AS Entity1Name FROM ' + @DataForReplacement 
									+ ' INNER JOIN Attributes A '
									+ ' ON CAST(' + @AliasName + '.' + @Entity1Link + ' AS NVARCHAR) = A.AttributeValueString ' 
									+ ' INNER JOIN DefinedAttributes DA ON DA.AttributeID = A.AttributeID AND DA.AttributeName = "' + @Entity1Link + '"'
									+ ' INNER JOIN Entities E3 ' 
									+ ' ON A.EntityID = E3.EntityID) AS ' + @AliasName
		END ELSE BEGIN
			SET @DataForReplacement = 'SELECT * FROM (' + @QueryString + ') WHERE  ' 
									+ '(""@ED"" IS NULL OR StartDate<=TO_DATE(""@ED"",''''MM/DD/YYYY'''') OR StartDate IS NULL) '
									+ 'AND (""@SD"" IS NULL OR EndDate>=TO_DATE(""@SD"",''''MM/DD/YYYY'''') OR EndDate IS NULL) '
			SET @DataForReplacement = 'OPENQUERY('+@SystemName+',''SELECT * FROM (' + @DataForReplacement + ') '
									+ @AliasName + ' ' + COALESCE(@WhereClause,'') + ''') AS ' + @AliasName + ' '
		END
	END ELSE BEGIN
		SET @DataForReplacement = 'SELECT * FROM (' + @QueryString + ') '
		IF NOT @Entity1LinkValue IS NULL
			SET @DataForReplacement = @DataForReplacement + 'WHERE (' + @Entity1Link + ' = ""' + @Entity1LinkValue
								+ '"" OR ""' + @Entity1LinkValue + '"" IS NULL) '
		SET @DataForReplacement = 'OPENQUERY('+@SystemName+',''SELECT * FROM (' + @DataForReplacement + ') '
						+ @AliasName + ' ' + COALESCE(@WhereClause,'') + ''') AS ' + @AliasName + ' '
	END

	--SET @DataForReplacement = 'OPENQUERY('+@SystemName+',''SELECT * FROM (' + @QueryString + ') ' + @AliasName + ' ' + COALESCE(@WhereClause,'') + ''') AS ' + @AliasName + ' '

	-- Return the result of the function
	RETURN @DataForReplacement

END